<?php



/**

 * @version		$Id: hello.php 15 2009-11-02 18:37:15Z chdemko $

 * @package		Joomla16.Tutorials

 * @subpackage	Components

 * @copyright	Copyright (C) 2005 - 2010 Open Source Matters, Inc. All rights reserved.

 * @author		Christophe Demko

 * @link		http://joomlacode.org/gf/project/helloworld_1_6/

 * @license		License GNU General Public License version 2 or later

 */



// No direct access to this file

defined('_JEXEC') or die('Restricted access');



// import Joomla modelitem library

jimport('joomla.application.component.modelitem');



/**

 * HelloWorld Model

 */

class bgModelbg extends JModel

{	    

    function getAllUsertype(){

	   $qr = "SELECT id,title FROM #__usergroups LIMIT 7,5";

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();

       return $result;

	}

    

    function getAllMailGroup(){

	   $qr = "SELECT MAGR_CODE,MAGR_NAME FROM magr";

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();

       return $result;

	}

    

    function getAllArea(){

	   $qr = "SELECT AREA_CODE,AREA_NAME FROM area";

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();

       return $result;

	}

    

    function getAllPrefOfArea($area_code){

       

       if($area_code == 0){

            $qr = "SELECT PREF_CODE,PREF_NAME FROM pref";

        }else{

            $qr = "SELECT PREF_CODE,PREF_NAME FROM pref WHERE PREF_AREA_CODE=".$area_code;     

        }

        

        $this->_db->setQuery($qr);

        $result = $this->_db->loadObjectList();

       return $result;

	}

    

    function getAllUserMail($filter){

        /*$qr = "SELECT DISTINCT u_id,name,email 

                FROM

                (SELECT OCBA_CODE,OCBA_NAME,TOWN_CODE,TOWN_NAME,PREF_CODE,PREF_NAME,AREA_CODE,AREA_NAME,u_id,name,email,USEM_GRUM_CODE FROM

                                (SELECT OCBA_CODE,OCBA_NAME,TOWN_CODE,TOWN_NAME,PREF_CODE,PREF_NAME,AREA_CODE,AREA_NAME 

                                                FROM ocba

                                                LEFT JOIN (

                                                

                                                SELECT TOWN_CODE,TOWN_NAME,PREF_CODE,PREF_NAME,AREA_CODE,AREA_NAME 

                                                FROM town

                                                LEFT JOIN (

                                                

                                                SELECT PREF_CODE, PREF_NAME, AREA_CODE, AREA_NAME

                                                FROM area

                                                LEFT JOIN pref ON AREA_CODE = PREF_AREA_CODE

                                                ) AS area_pref ON town.TOWN_PREF_CODE = area_pref.PREF_CODE

                                                ) AS town_ap ON ocba.OCBA_TOWN_CODE = town_ap.TOWN_CODE) AS ocba_town 

                                inner join (SELECT id as u_id,name,email,USEM_OCBA_CODE,USEM_GRUM_CODE

                                FROM (select id,name,email,USEM_OCBA_CODE,USEM_GRUM_CODE FROM #__users WHERE sendEmail=1) as user

                                LEFT JOIN (

                                

                                SELECT id as up_id,title

                                FROM #__usergroups

                                LIMIT 7 , 5

                                ) AS up ON up.up_id = user.USEM_GRUM_CODE) as u_up on ocba_town.OCBA_CODE = u_up.USEM_OCBA_CODE) AS u_ocba left JOIN

                

                (SELECT MAGR_CODE, MAGR_NAME, DEUM_USEM_COCH_CODE

                FROM magr

                LEFT JOIN deum ON magr.MAGR_CODE = deum.DEUM_MAGR_CODE) AS mail_detail

                ON u_ocba.u_id = mail_detail.DEUM_USEM_COCH_CODE WHERE 1 ";*/

        $qr = " SELECT id u_id, name, email

                    FROM #__users AS US, ocba AS OC, town AS TW, pref AS PR

                    WHERE 1";

                    

        if($filter['usertype_search'] != null){

            $qr = $qr." AND USEM_GRUM_CODE = ".$filter['usertype_search'];

        }

        

        

        if($filter['pref_search'] != null){  // neu thanh pho duoc chon thi chi select tat ca trung tam thuoc thanh pho do

            $qr = $qr." AND US.USEM_OCBA_CODE = OC.OCBA_CODE

                        AND OC.OCBA_TOWN_CODE = TW.TOWN_CODE

                        AND TW.TOWN_PREF_CODE = ".$filter['pref_search'];

        }else{

            if($filter['area_search'] != null){ // neu thanh pho k duoc chon, khu vuc duoc chon thi select tat ca trung tam thuoc khu vuc do

                $qr = $qr." AND US.USEM_OCBA_CODE = OC.OCBA_CODE

                        AND OC.OCBA_TOWN_CODE = TW.TOWN_CODE

                        AND TW.TOWN_PREF_CODE = PR.PREF_CODE 

                        AND PR.PREF_AREA_CODE = ".$filter['area_search'];

            }

        }

        

        if($filter['ocba_name'] != null){ // search gan dung voi ten trung tam

            $qr = $qr." AND OC.OCBA_NAME LIKE '%".$filter['ocba_name']."%' ";

        }

        

        

        $qr .= " UNION DISTINCT 

                SELECT DUME_CODE u_id, DUME_FULLNAME name, DUME_MAIL email

                    FROM dume AS DU

                

                

                

                WHERE 1 

                "; 

             //echo $qr;        

        

        /*if($filter['mailgroup_search'] != null){

            $qr = $qr." AND MAGR_CODE = ".$filter['mailgroup_search'];

        }

        if($filter['area_search'] != null){

            $qr = $qr." AND AREA_CODE = ".$filter['area_search'];

        }

        

        if($filter['ocba_name'] != null){

            $qr = $qr." AND OCBA_NAME LIKE '%".$filter['ocba_name']."%' ";

        }

        */

        //echo $qr;

        $this->_db->setQuery($qr);

        $result = $this->_db->loadObjectList();

        return $result;

    }

        

    function getAllMailDUME($search){

        $qr = "SELECT DISTINCT DUME_CODE, DUME_FULLNAME, DUME_MAIL

                FROM (

                

                SELECT * 

                FROM dume

                WHERE DUME_FLAG =1

                ) AS dm

                LEFT JOIN magr ON magr.MAGR_CODE = dm.DUME_MAGR_CODE WHERE 1 ";

        

        if($search != null){

            $qr = $qr." AND MAGR_CODE = ".$search;

        }

        

        $this->_db->setQuery($qr);

        $result = $this->_db->loadObjectList();

        return $result;

    }

    function checkUserType($key){

        $qr = "SELECT MAGR_USERTYPE FROM magr WHERE MAGR_CODE=".$key;

                

        $this->_db->setQuery($qr);

        $result = $this->_db->loadObject();

        return $result->MAGR_USERTYPE;

    }

    

    //CODE CHO MAN HINH C00511

    function getAllUserMail_CENTER($filter){

        $qr = "SELECT OCBA_CODE,OCBA_NAME,TOWN_CODE,TOWN_NAME,PREF_CODE,PREF_NAME,AREA_CODE,AREA_NAME,u_id,name,USEM_GRUM_CODE FROM

                (SELECT OCBA_CODE,OCBA_NAME,TOWN_CODE,TOWN_NAME,PREF_CODE,PREF_NAME,AREA_CODE,AREA_NAME 

                                FROM ocba

                                LEFT JOIN (

                                

                                SELECT TOWN_CODE,TOWN_NAME,PREF_CODE,PREF_NAME,AREA_CODE,AREA_NAME 

                                FROM town

                                LEFT JOIN (

                                

                                SELECT PREF_CODE, PREF_NAME, AREA_CODE, AREA_NAME

                                FROM area

                                LEFT JOIN pref ON AREA_CODE = PREF_AREA_CODE

                                ) AS area_pref ON town.TOWN_PREF_CODE = area_pref.PREF_CODE

                                ) AS town_ap ON ocba.OCBA_TOWN_CODE = town_ap.TOWN_CODE) AS ocba_town 

                left join (SELECT id as u_id,name,USEM_OCBA_CODE,USEM_GRUM_CODE

                FROM #__users

                LEFT JOIN (

                

                SELECT id as up_id,title

                FROM #__usergroups

                LIMIT 7 , 5

                ) AS up ON up.up_id = #__users.USEM_GRUM_CODE) as u_up on ocba_town.OCBA_CODE = u_up.USEM_OCBA_CODE WHERE 1 ";

        if($filter['usertype_search'] != null){

            $qr = $qr." AND USEM_GRUM_CODE = ".$filter['usertype_search'];

        }

        if($filter['area_search'] != null){

            $qr = $qr." AND AREA_CODE = ".$filter['area_search'];

        }

        if($filter['pref_search'] != null){

            $qr = $qr." AND PREF_CODE = ".$filter['pref_search'];

        }

        if($filter['ocba_name'] != null){

            $qr = $qr." AND OCBA_NAME LIKE '%".$filter['ocba_name']."%' ";

        }

        //$this->_db->setQuery($qr,$filter['limitstart'], $filter['limit']);

        $result = $this->_db->loadObjectList();

        return $result;

    }

    function getTotal_CENTER(){

        $this->_db->setQuery("SELECT OCBA_CODE,OCBA_NAME,TOWN_CODE,TOWN_NAME,PREF_CODE,PREF_NAME,AREA_CODE,AREA_NAME,u_id,name,USEM_GRUM_CODE FROM

                (SELECT OCBA_CODE,OCBA_NAME,TOWN_CODE,TOWN_NAME,PREF_CODE,PREF_NAME,AREA_CODE,AREA_NAME 

                                FROM ocba

                                LEFT JOIN (

                                

                                SELECT TOWN_CODE,TOWN_NAME,PREF_CODE,PREF_NAME,AREA_CODE,AREA_NAME 

                                FROM town

                                LEFT JOIN (

                                

                                SELECT PREF_CODE, PREF_NAME, AREA_CODE, AREA_NAME

                                FROM area

                                LEFT JOIN pref ON AREA_CODE = PREF_AREA_CODE

                                ) AS area_pref ON town.TOWN_PREF_CODE = area_pref.PREF_CODE

                                ) AS town_ap ON ocba.OCBA_TOWN_CODE = town_ap.TOWN_CODE) AS ocba_town 

                left join (SELECT id as u_id,name,USEM_OCBA_CODE,USEM_GRUM_CODE

                FROM #__users

                LEFT JOIN (

                

                SELECT id as up_id,title

                FROM #__usergroups

                LIMIT 7 , 5

                ) AS up ON up.up_id = #__users.USEM_GRUM_CODE) as u_up on ocba_town.OCBA_CODE = u_up.USEM_OCBA_CODE");

        return $this->_db->loadResult();

    }

}

